<%@ page language="java" contentType="application/vnd.ms-excel; charset=UTF-8" %>
<%@ page import="weaver.hrm.report.schedulediff.HrmScheduleDiffManager"%>
<%@ page import="weaver.systeminfo.*,java.util.*,weaver.hrm.*" %>
<%@ page import="weaver.general.Util,weaver.hrm.common.*" %>
<%@ page import="weaver.hrm.attendance.domain.*"%>
<!-- modified by wcd 2014-07-24 [E7 to E8] -->
<jsp:useBean id="colorManager" class="weaver.hrm.attendance.manager.HrmLeaveTypeColorManager" scope="page" />
<jsp:useBean id="rs" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="rs1" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="rs2" class="weaver.conn.RecordSet" scope="page" />
<jsp:useBean id="strUtil" class="weaver.common.StringUtil" scope="page"/>
<jsp:useBean id="dateUtil" class="weaver.common.DateUtil" scope="page"/>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<%
	User user = HrmUserVarify.getUser (request , response) ;
	if(user == null)  return ;
	response.setContentType("application/vnd.ms-excel");
	
	Calendar today = Calendar.getInstance ();
	String currentDate = Util.add0(today.get(Calendar.YEAR), 4) + "-"
					   + Util.add0(today.get(Calendar.MONTH) + 1, 2) + "-"
					   + Util.add0(today.get(Calendar.DAY_OF_MONTH), 2);//当前时间

	String cmd = strUtil.vString(request.getParameter("cmd"));//用于区分
	String fromDate = strUtil.vString(request.getParameter("fromDate"));//开始时间
	String toDate = strUtil.vString(request.getParameter("toDate"));//结束时间
	
	String XMNo = Util.null2String(request.getParameter("XMNo"));//项目编号
	String tnum = strUtil.vString(request.getParameter("tnum"));
	String subCompanyId = strUtil.vString(request.getParameter("subCompanyId"));//分部id
	String departmentId = strUtil.vString(request.getParameter("departmentId"));//部门id
	String resourceId = strUtil.vString(request.getParameter("resourceId"));//人员id
	String status = strUtil.vString(request.getParameter("status"));//状态id
	//非考勤管理员只能看到自己的记录
	if(!HrmUserVarify.checkUserRight("BohaiInsuranceScheduleReport:View", user)){
		resourceId = String.valueOf(user.getUID());
	}
	
	String fileName = "项目总监报表";
	response.setHeader("Content-disposition","attachment;filename="+new String(fileName.getBytes("GBK"),"iso8859-1")+".xls");
%>
<%
	if(cmd.equals("CheckWorkAttendance")){
%>
<table  border=1  bordercolor=black style="border-collapse:collapse;" width="100%" >
	<tbody>
		<tr><td align="center" colspan="23"><%=fileName%></td></tr>
		<tr>
			<td align="center" colspan="8" rowspan="2">项目基本信息</td>
			<td align="center" colspan="9">项目费用信息</td>
			<td align="center" colspan="5">项目进度信息</td>
			<td align="center" colspan="1">文件</td>
			<!--<td align="center" rowspan="2">其他</td>-->
		</tr>
		<tr>
			<td align="center" colspan="2">项目应收金额</td>
			<td align="center" colspan="6">项目应付金额</td>
			<td align="center">利润</td>
			<td align="center" colspan="1">项目部</td>
			<td align="center" colspan="4">手续部</td>
			<td align="center" colspan="1">项目部</td>
		</tr>
		<tr>
			<td align="center">序号</td>
			<td align="center">项目编号</td>
			<td align="center">项目名称</td>
			<td align="center">项目区域</td>
			<td align="center">项目类型</td>
			<td align="center">项目种类</td>
			<td align="center">项目经理</td>
			<td align="center">施工班组</td>
			<td align="center">工程造价</td>
			<td align="center">变更金额</td>
			<td align="center">消防费用</td>
			<td align="center">环保费用</td>
			<td align="center">招牌费用</td>
			<!--<td align="center">卫生费用</td>-->
			<td align="center">其他成本费用</td>
			<td align="center">班组承包价格</td>
			<td align="center">后加金额</td>
			<td align="center">毛利率</td>
			<td align="center">入场时间</td>
			<!--<td align="center">设备进场时间</td>
			<td align="center">交铺时间</td>
			<td align="center">项目现状</td>
			<td align="center">项目进度</td>-->
			<td align="center">消防</td>
			<td align="center">环保</td>
			<td align="center">招牌</td>
			<td align="center">卫生</td>
			<td align="center">施工保险日期</td>
			<!--<td align="center">竣工资料完成情况</td>
			<td align="center">备注</td>-->
		</tr>
		<%
			String sql = "select a.xmbh,a.xmmc,b.selectname scqy,c.selectname xmzl,d.lastname xmjl,d.kgsj,e.bszzjg,e.mll,e.bxkssj,e.bxjssj,f.bgbsjg,f.bgze,g.htje,h.dec56,h.dec73,h.dec41,h.dec46,i.gscbfy";
			sql += " from formtable_main_42 a left join (select selectvalue,selectname from workflow_selectitem where fieldid='9697') b on a.scqy=b.selectvalue";
			sql += " left join (select selectvalue,selectname from workflow_selectitem where fieldid='8652') c on a.xmzl=c.selectvalue";
			sql += " left join (select d.xmbh,e.lastname,d.kgsj from formtable_main_54 d left join HrmResource e on d.xmjl=e.id) d on a.xmbh=d.xmbh";
			sql += " left join formtable_main_48 e on a.xmbh=e.xmbh";
			sql += " left join (select f.xmbh,sum(f.bgbsjg) bgbsjg,sum(f.bgze) bgze from formtable_main_57 f group by f.xmbh) f on a.xmbh=f.xmbh";
			sql += " left join (select g.xmbh,sum(g.htje) htje from formtable_main_70 g group by g.xmbh) g on a.xmbh=g.xmbh";
			sql += " left join formtable_main_51 h on a.xmbh=h.xmbh";
			sql += " left join (select i.xmbh,sum(i.gscbfy) gscbfy from formtable_main_45 i group by i.xmbh) i on a.xmbh=i.xmbh";
			
			if(!fromDate.equals("") && !toDate.equals("")){
				sql += " where a.sqrq between '"+fromDate+"' and '"+toDate+"'";
			}else if(!fromDate.equals("")){
				sql += " where a.sqrq='"+fromDate+"'";
			}else if(!toDate.equals("")){
				sql += " where a.sqrq='"+toDate+"'";
			}
			
			if(!fromDate.equals("") || !toDate.equals("")){
				if(!XMNo.equals("")){
					sql += " and a.xmbh like '%"+XMNo+"%'";
				}
			}else{
				if(!XMNo.equals("")){
					sql += " where a.xmbh like '%"+XMNo+"%'";
				}
			}
			sql += " order by a.xmbh";
			rs.executeSql(sql);
			int xh = 0;
			while(rs.next()){
				xh = xh+1;
				String xmbh = strUtil.vString(rs.getString("xmbh"));
				out.print("<td align='center'>"+xh+"</td>");
				out.print("<td align='center'>"+xmbh+"</td>");//项目编号
				out.print("<td align='center'>"+rs.getString("xmmc")+"</td>");//项目名称
				out.print("<td align='center'>"+rs.getString("scqy")+"</td>");//项目区域
				out.print("<td align='center'></td>");//项目类型
				out.print("<td align='center'>"+rs.getString("xmzl")+"</td>");//项目种类
				out.print("<td align='center'>"+rs.getString("xmjl")+"</td>");//项目经理
				
				String sgbz = "";
				String bzxm = "";
				String kgbgSql = "select bzxm from formtable_main_48 a,formtable_main_48_dt1 b where a.id=b.mainid and a.xmbh='"+xmbh+"'";
				rs1.executeSql(kgbgSql);
				while(rs1.next()){
					 String a = strUtil.vString(rs1.getString("bzxm"));
					 if(!bzxm.equals(a)){
						bzxm = a;
						String hrmSql = "select lastname from HrmResource where id='"+bzxm+"'";
						rs2.executeSql(hrmSql);
						while(rs2.next()){
							if(!sgbz.equals("")){
								sgbz += ","+strUtil.vString(rs2.getString("lastname"));
							}else{
								sgbz += strUtil.vString(rs2.getString("lastname"));
							}
						}
					 }
				}
				out.print("<td align='center'>"+sgbz+"</td>");//施工班组
				
				out.print("<td align='center'>"+rs.getString("bszzjg")+"</td>");//工程造价
				out.print("<td align='center'>"+rs.getString("bgbsjg")+"</td>");//变更金额
				
				String xfhtje = "";//合同价格-消防
				String hbhtje = "";//合同价格-环保
				String zphtje = "";//合同价格-招牌
				
				String htSql = "select fbqk,htyf,htje from formtable_main_70 where xmbh='"+xmbh+"'";
				rs1.executeSql(htSql);
				while(rs1.next()){
					String fbqk = strUtil.vString(rs1.getString("fbqk"));//分包情况
					if(fbqk.equals("0")){//消防
						xfhtje = strUtil.vString(rs1.getString("htje"));
					}else if(fbqk.equals("1")){//环保
						hbhtje = strUtil.vString(rs1.getString("htje"));
					}else if(fbqk.equals("2")){//招牌
						zphtje = strUtil.vString(rs1.getString("htje"));
					}
				}
				out.print("<td align='center'>"+xfhtje+"</td>");//消防费用
				out.print("<td align='center'>"+hbhtje+"</td>");//环保费用
				out.print("<td align='center'>"+zphtje+"</td>");//招牌费用
				//out.print("<td align='center'></td>");//卫生费用
				out.print("<td align='center'>"+rs.getString("gscbfy")+"</td>");//其他成本费用
				out.print("<td align='center'>"+rs.getString("htje")+"</td>");//班组承包价格
				out.print("<td align='center'>"+rs.getString("bgze")+"</td>");//后加金额
				out.print("<td align='center'>"+rs.getString("mll")+"</td>");//毛利率
				out.print("<td align='center'>"+rs.getString("kgsj")+"</td>");//入场时间
				/**
				out.print("<td align='center'></td>");//设备进场时间
				out.print("<td align='center'></td>");//交铺时间
				out.print("<td align='center'></td>");//项目现状
				out.print("<td align='center'></td>");//项目进度
				*/
				out.print("<td align='center'>"+rs.getString("dec56")+"</td>");//消防
				out.print("<td align='center'>"+rs.getString("dec73")+"</td>");//环保
				out.print("<td align='center'>"+rs.getString("dec41")+"</td>");//招牌
				out.print("<td align='center'>"+rs.getString("dec46")+"</td>");//卫生
				
				String bxkssj = strUtil.vString(rs.getString("bxkssj"));//保险开始日期
				String bxjssj = strUtil.vString(rs.getString("bxjssj"));//保险结束日期
				if(!bxkssj.equals("") && !bxjssj.equals("")){
					out.print("<td align='center'>"+bxkssj+"至"+bxjssj+"</td>");//施工保险日期
				}else if(!bxkssj.equals("")){
					out.print("<td align='center'>"+bxkssj+"</td>");//施工保险日期
				}else if(!bxjssj.equals("")){
					out.print("<td align='center'>"+bxjssj+"</td>");//施工保险日期
				}else{
					out.print("<td align='center'></td>");//施工保险日期
				}
				/**
				out.print("<td align='center'></td>");//竣工资料完成情况
				out.print("<td align='center'></td>");//备注
				*/
				out.print("</tr>");
			}
		%>
		<tr>
			<td colspan="23" align="right" ><%=SystemEnv.getHtmlLabelName(20087,user.getLanguage())+"："+currentDate%></td>
		</tr>
	</tbody>
</table>
<%
	}
%>